﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Xml;
using System.Data;
using Kailua.WSSLists;
using System.Web;
using System.Data.SqlTypes;
using net.windward.utils;
using System.Web.Services.Protocols;
using log4net;

namespace Kailua.net.windward.utils.ado.SPList.custom
{
    /// <summary>
    /// A command for SharePoint list data providers.
    /// </summary>
    public class SPListCommand : DbCommand
    {
        #region Fields

        /// <summary>
        /// the CAML Query element
        /// </summary>
        private XmlDocument query = new XmlDocument();
        /// <summary>
        /// the CAML ViewFields element
        /// </summary>
        private XmlDocument viewFields = new XmlDocument();
        /// <summary>
        /// the connection this command operates on
        /// </summary>
        private SPListConnection _conn;
        
        private int commandTimeout;

        private CommandType commandType;

        private SPListParameterCollection parameters;

        private static ILog log = LogManager.GetLogger(typeof(SPListCommand));

        #endregion Fields

        #region Constructors
        /// <summary>
        /// 
        /// </summary>
        public SPListCommand()
            : base()
        {
            parameters = new SPListParameterCollection();
            loadCamlElements();
        }
        /// <summary>
        /// loads the CAML elements like query, viewFields, ...
        /// </summary>
        private void loadCamlElements()
        {
            query = new XmlDocument();
            viewFields = new XmlDocument();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdText"></param>
        public SPListCommand(string cmdText)
            : base()
        {
            parameters = new SPListParameterCollection();
            loadCamlElements();
            CommandText = cmdText;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="conn"></param>
        public SPListCommand(string cmdText, SPListConnection conn)
            : base()
        {
            parameters = new SPListParameterCollection();
            this.Connection = conn;
            loadCamlElements();
            CommandText = cmdText;
        }
        #endregion Constructors

        #region IDbCommand Members

        /// <summary>
        /// Not supported.
        /// </summary>
        public override void Cancel()
        {
            throw new NotSupportedException();
        }

        /// <summary>
        /// Gets or sets the command text.
        /// </summary>
        public override string CommandText
        {
            // bugbug: this looks incorrect; why take in the whole xml doc and only return a subnode?
            get
            {
                return query.OuterXml;
            }
            set
            {
                // load the list query
                XmlDocument doc = new XmlDocument();
                doc.LoadXml(value);

                // extract the Query element
                XmlNode queryNode = doc.SelectSingleNode("/listq/Query");
                query.LoadXml(queryNode.OuterXml);

                // extract the ViewFields element
                XmlNode viewFieldsNode = doc.SelectSingleNode("/listq/ViewFields");
                if (viewFieldsNode != null)
                    viewFields.LoadXml(viewFieldsNode.OuterXml);

                // extract the list guid element
                XmlNode listNode = doc.SelectSingleNode("/listq/list/guid");
                if (listNode != null)
                    _conn.listServiceAdapter.sListGuid = listNode.InnerText;
            }
        }

        /// <summary>
        /// Gets or sets the command timeout value.
        /// </summary>
        public override int CommandTimeout
        {
            get { return commandTimeout; }
            set { commandTimeout = value; }
        }

        /// <summary>
        /// Gets or sets the command type.
        /// </summary>
        public override CommandType CommandType
        {
            get { return commandType; }
            set { commandType = value; }
        }

        /// <summary>
        /// Not supported.
        /// </summary>
        /// <returns></returns>
        public override int ExecuteNonQuery()
        {
            throw new NotSupportedException();
        }

        /// <summary>
        /// Executes the list service query and returns an SPListDataReader.
        /// </summary>
        /// <returns></returns>
        private DbDataReader createSPListDataReader()
        {
            DataSet dataSet = executeAndCreateDataSet();

            // return the SPListDataReader
            return new SPListDataReader(dataSet.CreateDataReader());
        }

        /// <summary>
        /// Executes the query, and returns the first column of the first row in the resultset
        /// returned by the query. Extra columns or rows are ignored.
        /// </summary>
        /// <returns></returns>
        public override object ExecuteScalar()
        {
            // execute query, load DataSet
            DataSet dataSet = executeAndCreateDataSet();

            // get the first item of the first record
            using (DataTableReader reader = dataSet.CreateDataReader())
            {
                if (!reader.HasRows) return null;

                if (reader.Read()) return reader[0];

                return null;
            }
        }

        /// <summary>
        /// Not supported.
        /// </summary>
        public override void Prepare()
        {
            throw new NotSupportedException();
        }

        /// <summary>
        /// Not supported.
        /// </summary>
        public override UpdateRowSource UpdatedRowSource
        {
            get
            {
                throw new NotSupportedException();
            }
            set
            {
                throw new NotSupportedException();
            }
        }

        #endregion

        #region IDisposable Members

        /// <summary>
        /// 
        /// </summary>
        public void Dispose()
        {

        }

        #endregion


        /// <summary>
        /// Executes the query to the list service and creates a DataSet containing the query results.
        /// </summary>
        /// <returns></returns>
        private DataSet executeAndCreateDataSet()
        {
            try
            {
                // prep a list service query
                SPListWebServiceAdapter listServiceAdapter = prepListSvcQuery();

                // execute the query
                XmlNode listItems = listServiceAdapter.GetListItems();

                // load the query result into a DataSet
                DataSet dataSet = CreateDataSet(listItems, listServiceAdapter);
                return dataSet;
            }
            catch (SoapException ex)
            {
                string detail = ex.Detail.OuterXml;
                throw new Exception(detail, ex);
            }
            catch (Exception ex)
            {
                if (log.IsWarnEnabled)
                    log.Warn("Error occured creating SharePoint list dataset.", ex);
                throw;
            }
        }

        /// <summary>
        /// Executes the query to the list service and creates a DataTable containing the query results.
        /// </summary>
        /// <returns></returns>
        internal DataTable executeAndCreateDataTable()
        {
            try
            {
                // prep a list service query
                SPListWebServiceAdapter listServiceAdapter = prepListSvcQuery();

                // execute the query
                XmlNode listItems = listServiceAdapter.GetListItems();

                // get the schema
                DataTable schemaTable = CreateSchemaDataTable(listServiceAdapter);

                // load the query result into a DataSet
                return CreateDataTable(listItems, schemaTable);
            }
            catch (SoapException ex)
            {
                string detail = ex.Detail.OuterXml;
                throw new Exception(detail, ex);
            }
            catch (Exception ex)
            {
                if (log.IsWarnEnabled)
                    log.Warn("Error occured creating SharePoint list data table.", ex);
                throw;
            }
        }
        /// <summary>
        /// Creates the schema data table
        /// </summary>
        /// <param name="listServiceAdapter"></param>
        /// <returns></returns>
        private DataTable CreateSchemaDataTable(SPListWebServiceAdapter listServiceAdapter)
        {
            try
            {
                // get the schema (note: moved out of if-statement because this will be used in the future for type casting)
                Lists listService = new Lists();
                listService.PreAuthenticate = true;
                listService.Url = listServiceAdapter.serviceUrl;
                listService.Credentials = listServiceAdapter.credentials;
                XmlNode ndListSchema = listService.GetListAndView(listServiceAdapter.sListGuid, null);
                XmlDocument docListSchema = new XmlDocument();
                docListSchema.LoadXml(ndListSchema.OuterXml);
                XmlNamespaceManager nsmgr = new XmlNamespaceManager(docListSchema.NameTable);
                nsmgr.AddNamespace("df", "http://schemas.microsoft.com/sharepoint/soap/");

                // build the schema table
                DataTable schemaTable = new DataTable();
                // if the ViewFields are not specified, then we'll just use the default view
                if (string.IsNullOrEmpty(viewFields.OuterXml))
                {
                    foreach (XmlNode ndCol in docListSchema.SelectNodes("/df:ListAndView/df:View/df:ViewFields/df:FieldRef", nsmgr))
                    {
                        addColToSchema(schemaTable, ndCol);
                    }
                }
                else
                {
                    // use the ViewFields to determine the schema
                    foreach (XmlNode ndCol in viewFields.SelectNodes("/ViewFields/FieldRef"))
                    {
                        addColToSchema(schemaTable, ndCol);
                    }
                }
                return schemaTable;
            }
            catch (SoapException ex)
            {
                string detail = ex.Detail.OuterXml;
                throw new Exception(detail, ex);
            }
            catch (Exception ex)
            {
                if (log.IsWarnEnabled)
                    log.Warn("Error occured creating schema data table for SharePoint list query.", ex);
                throw;
            }
        }
        /// <summary>
        /// Adds the specified column info to the specifed schema table
        /// </summary>
        /// <param name="schemaTable"></param>
        /// <param name="ndCol"></param>
        private static void addColToSchema(DataTable schemaTable, XmlNode ndCol)
        {
            // get the name
            XmlAttribute atName = ndCol.Attributes["Name"];
            //bugbug: should this ever happen?
            if (atName == null)
                return;
            string name = atName.Value;
            schemaTable.Columns.Add(name);
        }

        /// <summary>
        /// Creates a DataSet from the XML SPListItem document.
        /// </summary>
        /// <param name="listItems">the XML returned from a query to the SPListWebServiceAdapter</param>
        /// <param name="listServiceAdapter"></param>
        /// <returns></returns>
        private DataSet CreateDataSet(XmlNode listItems, SPListWebServiceAdapter listServiceAdapter)
        {
            DataSet dataSet = new DataSet();
            dataSet.Tables.Add(CreateDataTable(listItems, CreateSchemaDataTable(listServiceAdapter)));

            return dataSet;
        }

        /// <summary>
        /// Creates a DataTable from the XML SPListItem document.
        /// </summary>
        /// <param name="listItems"></param>
        /// <param name="schemaTable"></param>
        /// <returns></returns>
        private static DataTable CreateDataTable(XmlNode listItems, DataTable schemaTable)
        {
            DataTable dt = schemaTable;

            // get the data node
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(listItems.OuterXml);
            XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
            nsmgr.AddNamespace("df", "http://schemas.microsoft.com/sharepoint/soap/"); //default namespace
            nsmgr.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");
            XmlNode dataNode = doc.SelectSingleNode("/df:listitems/rs:data", nsmgr);

            // read the list item xml and construct a DataTable as you go
            foreach (XmlNode n in dataNode.ChildNodes)
            {
                if (n is XmlElement && n.Name.Equals("z:row"))
                {
                    XmlElement rowElem = (XmlElement)n;
                    DataRow newRow = dt.NewRow();
                    dt.Rows.Add(newRow);
                    foreach (XmlAttribute attr in rowElem.Attributes)
                    {
                        // strip the ows_ prefix; not sure if they always have this, so check first
                        string attrName = attr.Name.StartsWith("ows_") ? attr.Name.Substring(4) : attr.Name;
                        // this part we used when we were building this data table dynamically
                        // now we instead use the passed in schemaTable, so we don't add any columns
                        //if (!dt.Columns.Contains(attrName))
                        //    dt.Columns.Add(attrName);
                        // this is just a safety check, in case GetListItems returns something not found in GetList
                        if (dt.Columns.Contains(attrName)) 
                            newRow[attrName] = attr.Value;
                    }
                }
            }

            return dt;
        }

        /// <summary>
        /// Prepares an SPListWebServiceAdapter for query to the list service.
        /// </summary>
        /// <returns></returns>
        private SPListWebServiceAdapter prepListSvcQuery()
        {
            SPListWebServiceAdapter listService = ((SPListConnection)Connection).listServiceAdapter;
            // TODO: set the list service query, view fields, options, ...

            // swap parameters
            string PARAM_MARKER = "<param />";
            string currentXml = query.OuterXml;
            foreach (SPListParameter param in parameters)
            {
                int paramInd = currentXml.IndexOf(PARAM_MARKER);
                if (paramInd == -1)
                    break;
                currentXml = currentXml.Remove(paramInd, PARAM_MARKER.Length);
                string val = string.Empty;
                if (param.Value != null)
                    val =  HttpUtility.HtmlEncode(param.Value.ToString());
                currentXml = currentXml.Insert(paramInd, val);
            }
            query.LoadXml(currentXml);

            // load the query
            if (string.IsNullOrEmpty(query.OuterXml))
                listService.query = null;
            else
                listService.query = query;

            // load the view fields
            if (string.IsNullOrEmpty(viewFields.OuterXml))
                listService.viewFields = null;
            else
                listService.viewFields = viewFields;

            listService.rowLimit = "100";

            string sQueryOps = "<QueryOptions><IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns></QueryOptions>";
            XmlDocument ndQueryOptions = new XmlDocument();
            ndQueryOptions.LoadXml(sQueryOps);
            listService.queryOptions = ndQueryOptions;
            return listService;
        }

        /// <summary>
        /// Creates a new SPListParameter.
        /// </summary>
        /// <returns></returns>
        protected override DbParameter CreateDbParameter()
        {
            return new SPListParameter();
        }

        /// <summary>
        /// Gets the connection associated with this command.
        /// </summary>
        protected override DbConnection DbConnection
        {
            get
            {
                return _conn;
            }
            set
            {
                _conn = (SPListConnection)value;
            }
        }

        /// <summary>
        /// Gets the SPListParameterCollection.
        /// </summary>
        protected override DbParameterCollection DbParameterCollection
        {
            get { return parameters; }
        }

        /// <summary>
        /// Not implemented.
        /// </summary>
        protected override DbTransaction DbTransaction
        {
            get
            {
                throw new NotImplementedException();
            }
            set
            {
                throw new NotImplementedException();
            }
        }

        /// <summary>
        /// Not implemented.
        /// </summary>
        public override bool DesignTimeVisible
        {
            get
            {
                throw new NotImplementedException();
            }
            set
            {
                throw new NotImplementedException();
            }
        }

        /// <summary>
        /// Executes the command, generating a new SPListDataReader.
        /// </summary>
        /// <param name="behavior"></param>
        /// <returns></returns>
        protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
        {
            return createSPListDataReader();
        }
    }
}
